from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import fills,colors,NamedStyle,Font,Side,Border,PatternFill,Alignment,Protection
import numpy as np 
import pathlib
import os
import sqlite3
import basedata 
import mpmath
import time
root=pathlib.Path(__file__).parent
db=sqlite3.connect(str(root/"cj.db"))
cur = db.cursor()
group=basedata.group
km=basedata.km
zkm=[]
teacher=sqlite3.connect(str(root.parent/"timetable/classbook.db"))
teacher=teacher.cursor()
for k in km:
        zkm=zkm+km[k]
zkm=list(set(zkm))
print(zkm)
mcz=basedata.mcz
cprs=basedata.cprs
zh=basedata.zh
df={}
RecordNumber={}
xsw=3
名单人数={}

'''
def zh(km,df):
    if (km == "物理"):
        return df*0.5
    if (km == "化学" or km == "地理"):
        return df*0.3
    if (km == "道法"  or km == "历史" or km == "生物"):
        return df*0.4
    if (km == "信息" or km == "音乐"):
        if(df>=60):
            return 10
        else:
            return 0
    return df
'''
def saveWb(wb,k):
    qkt="折合汇总" if basedata.ScoreConversion == True else "不折合汇总"
    path=root/"output/{0}{1}.xlsx".format(k,qkt)
    try:

        wb.save(path)
    except PermissionError:
        a=input("文件{path}无法保存，请关闭文件！".format(path=path))
        saveWb(wb,k) 
    except Exception as e:
        print(str(e)+"close file")

bd = Border(
    left=Side(border_style="thin",
                color='000000'),
    right=Side(border_style="thin",
                color='000000'),
    top=Side(border_style="thin",
                color='000000'),
    bottom=Side(border_style="thin",
                color='000000')
                )
alignment=Alignment(horizontal='center',#水平'center', 'centerContinuous', 'justify', 'fill', 'general', 'distributed', 'left', 'right'
            vertical='center',#垂直'distributed', 'bottom', 'top', 'center', 'justify'
            text_rotation=0,#旋转角度0~180
            wrap_text=False,#文字换行
            shrink_to_fit=True,#自适应宽度，改变文字大小,上一项false
            indent=0)
alignment1=Alignment(horizontal='left',#水平'center', 'centerContinuous', 'justify', 'fill', 'general', 'distributed', 'left', 'right'
            vertical='center',#垂直'distributed', 'bottom', 'top', 'center', 'justify'
            text_rotation=0,#旋转角度0~180
            wrap_text=False,#文字换行
            shrink_to_fit=True,#自适应宽度，改变文字大小,上一项false
            indent=0)

for k in zkm:
    #print(k,":")
    wb=Workbook()
    ws=wb.active
    for g in group:
        print("正在统计{0}{1}".format(k,g))

        ws=wb.create_sheet("{0}".format(g))
        if(g not in km):
            continue
        if(k not in km[g]):
            continue
        col=2
        maxrow=0
        for b in group[g]:#统初始化
            #print(b)

            for i in b:
                df[i]=[]
                row=4
                sql="select count(`姓名`) from main where 班级='{0}'".format(i)
                result=cur.execute(sql)
                result=result.fetchall()
                名单人数[str(i)]=result[0][0]
                sql="select 姓名,{0} from main where 班级='{1}'".format(k,i)
                result=cur.execute(sql)
                list=result.fetchall()
                if(basedata.MissingExamination != None):
                    RecordNumber[str(i)]=len(list)
                else:
                    RecordNumber[str(i)]=0
                for l in list:
                    # print(l)
                    ws.cell(row,col,l[0])
                    if(l[1] != "缺考" and l[1] != None):
                        ws.cell(row,col+1,float(zh(k,float(l[1]))))
                        df[i].append(float(zh(k,float(l[1]))))
                    else:
                        if(basedata.MissingExamination != None):
                            df[i].append(float(zh(k,float(basedata.MissingExamination))))
                        ws.cell(row,col+1,"缺考")
                    row=row+1
                    '''
                zf=np.nansum(df[i])
                pjf=np.nanmean(df[i])
                print(k,g,i,zf)
                print(k,g,i,pjf)
                '''
                col=col+2
                if(row>maxrow):
                    maxrow=row
        ws.merge_cells(start_row=1, end_row=1, start_column=1, end_column=col-1)#合并

        for i in range(4,maxrow):
            ws.cell(i,1,i-3)
        col=2
        njfps={}
        njpjf={}
        cppjt={}
        for kt in group[g]:
            njzf=[]
            njcprs=0
            pbfb=[]
            colt=col
            for i in kt:
                if(basedata.alias.__contains__(str(i))):
                    ws.cell(2,col,"{0}班".format(basedata.alias[str(i)]))
                else:
                    ws.cell(2,col,"{0}班".format(i))
                ws.merge_cells(start_row=2, end_row=2, start_column=col, end_column=col+1)
                ws.cell(2,col).alignment=alignment  #居中
                ws.cell(3,col,"姓名")
                ws.cell(3,col+1,"成绩")
                ws.cell(maxrow,col,f"{np.nansum(df[i]):.{xsw}f}")
                ws.cell(maxrow+1,col,f"{np.nanmean(df[i]):.{xsw}f}")
                ws.cell(maxrow+2,col,len(df[i]))
                t=basedata.getTotal(i,df[i],k)
                t=max(t,RecordNumber[str(i)],名单人数[str(i)])
                ws.cell(maxrow+3,col,t)
                cppjt[i]=np.nansum(df[i])/t  #参评平均分
                pbfb.append(cppjt[i]) #参评平均分 合计
                ws.cell(maxrow+4,col,f"{cppjt[i]:.{xsw}f}")
                col=col+2
                njzf=njzf+df[i]
                njcprs=njcprs+t
            

            #njfps[str(kt)]=np.nansum(njzf)/njcprs  #年级平均分=年级总分/参评人数
            njfps[str(kt)]=np.mean(pbfb) #年级平均分=班级平均分/班级数
            ws.cell(maxrow+5,colt,f"{njfps[str(kt)]:.{xsw}f}")
            ws.merge_cells(start_row=maxrow+5, end_row=maxrow+5, start_column=colt, end_column=colt+(len(kt)*2)-1)
            ws.cell(maxrow+5,colt).alignment=alignment
        col=2
        if(basedata.rangeByGroup):  #是否分组排名
            groupTemp=group
        else:
            groupTemp={}
            groupTemp[g]=[[]]
            for ig in group[g]:
                groupTemp[g][0]+=ig
            groupTemp[g][0]=tuple(groupTemp[g][0])
        for kt in groupTemp[g]:
            sort={}
            for i in kt:
                sort[i]=cppjt[i]
            s=sorted(sort.items(), key=lambda sort: sort[1],reverse=True)
            mc={}
            n=0
            for i in s:
                mc[str(i[0])]=mcz[n]
                n=n+1
        for kt in group[g]:
            for i in kt:
                if(cppjt[i]-njfps[str(kt)]<0):
                    strs=f"罚{njfps[str(kt)]-cppjt[i]:.{xsw}f}分"
                else:
                    strs=f"奖{cppjt[i]-njfps[str(kt)]:.{xsw}f}分"
                ws.cell(maxrow+6,col,strs)
                ws.cell(maxrow+7,col,mc[str(i)])
                sql=f" select name from main where real_course='{k}' and class='{i}班';"
                result=teacher.execute(sql)
                result=result.fetchone()
                ws.cell(maxrow+8,col,result[0])
                for i1 in range(0,10):
                    if (i1!=5):
                        ws.merge_cells(start_row=maxrow+i1, end_row=maxrow+i1, start_column=col, end_column=col+1)
                col=col+2


        ws.cell(2,1,"序号")
        ws.merge_cells(start_row=2, end_row=3, start_column=1, end_column=1)
        ws.cell(maxrow,1,"总分")
        ws.cell(maxrow+1,1,"实际平均分")
        ws.cell(maxrow+2,1,"实考人数")
        ws.cell(maxrow+3,1,"参评人数")
        ws.cell(maxrow+4,1,"参评平均分")
        ws.cell(maxrow+5,1,"年级平均分")
        ws.cell(maxrow+6,1,"奖或罚")
        ws.cell(maxrow+7,1,"名次")
        ws.cell(maxrow+8,1,"科任教师")
        ws.cell(maxrow+9,1,"教师签字")
        ws.cell(1,1,"{2}{0}{1}{3}".format(g,k,basedata.titleH,basedata.titleF))

        ws.cell(1,1).font=Font(size=20) #字体
        ws.row_dimensions[1].height = 32   #行高
        ws.row_dimensions[maxrow+9].height = 32   #行高
        ws.cell(1,1).alignment=alignment  #居中
        ws.cell(2,1).alignment=alignment  #居中
        ws.cell(maxrow+9,1).alignment=alignment1  #居中
        ws.column_dimensions['A'].width = 12
        ws.freeze_panes="B4"
        for i in range(2,ws.max_row+1):
            for n in range(1,ws.max_column+1):
                ws.cell(i,n).border=bd
    wb.remove(wb["Sheet"])  
    saveWb(wb,k)

# a=input("已完成，按Enter退出")
        
for i in range(4):
    print('\r',f"已完成，{3-i}秒后退出",end='')
    time.sleep(1)







